create view vPledges as
    select min(Activity.ID)                                 ID,
           min(Activity.TRANSACTION_DATE)     TRANSACTION_DATE,
           min(Activity.SOURCE_CODE)              SOURCE_CODE,
           -sum(Trans.AMOUNT)                         AMOUNT,
           max(TRANS_NUMBER)                        TRANS_NUMBER,
           min(Activity.CAMPAIGN_CODE)          CAMPAIGN_CODE,
           min(Activity.SOLICITOR_ID)               SOLICITOR_ID,
           sum (Activity.TAXABLE_VALUE)          TAXABLE_VALUE,
           max(Activity.OTHER_CODE)               OTHER_CODE,
           max(Activity.DESCRIPTION)               DESCRIPTION,     
           max(UF_1)                                           UF_1,
           max(Trans.INSTALL_BILL_DATE)        INSTALL_BILL_DATE,
           max(Trans.OWNER_ORG_CODE)        OWNER_ORG_CODE,
           max(Activity.SEQN)                             SEQN,
           max(Trans.MERGE_CODE)                  MERGE_CODE,
           max(Trans.MEM_TRIB_ID)                  MEM_TRIB_ID,
           max(Trans.MEM_TRIB_NAME_TEXT)  MEM_TRIB_NAME_TEXT     
        from Trans LEFT OUTER JOIN Activity on Trans.ACTIVITY_SEQN = Activity.SEQN   
        where Trans.TRANSACTION_TYPE = 'DIST' and Trans.JOURNAL_TYPE = 'IN'
        and Trans.INSTALL_BILL_DATE is not null
        and Trans.SOURCE_SYSTEM = 'FR'
group by Trans.TRANS_NUMBER,Trans.OWNER_ORG_CODE

GO
GRANT REFERENCES ON  [dbo].[vPledges] TO [IMIS]
GRANT SELECT ON  [dbo].[vPledges] TO [IMIS]
GRANT INSERT ON  [dbo].[vPledges] TO [IMIS]
GRANT DELETE ON  [dbo].[vPledges] TO [IMIS]
GRANT UPDATE ON  [dbo].[vPledges] TO [IMIS]
GO